FI - Raghav - 2016-10-27 - 27620 - Spec - AMEX Remittance (Jingsha by 2017-02-28) #wipdoc #FIOutgoingpayments

SPECIFICATIONS

27620-Spec-AMEX Remittance


Purpose

AMEX remittance advice.

Admin Info

Purpose
 AMEX Remittance
Requested By
 Babu
Spec Created By
 Raghav
Spec Created Date
 10/27/2016
Spec QA by
 Raghav
Objects
 ZAMREM
ZFI_AMEX_REMITT_EXTRACT
ZFI_AMEX_REMIT
TDDAT
TVDIR
ZFI_AMEX_REMITS
ZAMEX_IBCP
ZAMEX_REMIT
Document Status
 WIP


Estimates


Sl.No
 Activity
 Estimation in Hours
1
 Research
 24
2
 Documentation
 08
3
 Development/ Config
 46
4
 Unit test in DEV
 30
5
 Unit test in QUA
 20
6
 Other activity

TOTAL
 128

References

Prior Tickets

[Provide links of prior associated Spec / Break Fix BOSS document(s)]



Documents

[Attach any document(s) received for the requirement(s)]

Remit Implementation IBCP - AE File Specs.docx
BANKING INFO.docx
AMEX remettence.txt


Spec Changes

[List the changes made to program after the approval of the original requirement along with the Date on which the change request was received and the name of the initiator]

Sl.
 Change Details
 Requested By
 Requested On
 Notes if any
1
 AMEX Payments no need separate payments and develop the custom program to generate the AMEX remittance file
 Babu
 11/15/2016
 Stopped by Babu on 11/30/2016
2
 AMEX remittance will run in background once a week or biweekly, which will include all the payments of the AMEX vendor till the date from the previous run
 Babu
 11/17/2016
 Stopped by Babu on 11/30/2016
3
 AMEX file will run in foreground immediate after the APP run, custom program input screen should have Run data and identification.
 Babu
 11/30/2016
4
 New payment method must be created for AMEX and assign to all company codes as per their respective payments
 Babu
 11/30/2016
5
 Invoice line item text will have fixed length and constant text before card details start Example: AMEX US IBCP 374922XXXXX8004
 Babu
 11/30/2016
6
 Error log for the unprocessed invoices with card mismatch
 Babu
 11/30/2016
7
 AMEX remittance file name NEWERA.USREMIT change to NEWERA.USREMIT_YYYYMMDDIDENT
 Babu
 12/02/2016
 File name change on 12/10/2016
8
 Add Test indicator in screen
 Babu
 12/10/2016
9
 New transaction for custome table ZAMEX_IBCP and program ZAMEX_REMIT
 Babu
 12/12/2016

Functional Requirement

Purpose/WHY:

[Explain the purpose of the project and the reason why this requirement has come]
Concur Travel & Expense Management AMEX payment remittance to AMEX bank
AMEX payment should have the separate payment method and generate remittance file to send the individual cards info to bank
Employees of NEWERA use AMEX card for Travel & Expense Management, while making payment to AMEX bank NEC have to send the information to remittance breakup for individual card payments.

Email conversation on AMEX remettance:
Dt: 10/27/2016 by NEC Jingsha
Here is the Amex remittance requirements. Please review and let us know if you have further questions. Meanwhile, we will try to request a sample file from the business. But we should start development even if the sample file is not available.

Dt: 11/10/2016 Sent requirements by BMS Raghav
Clarification on AMEX field mapping.xlsx
Questions on AMEX.docx

Dt: 11/11/16 by NEC Babu
Please start working on this program development, attached file has updated information with clarity on fields. Also start building custom table with ability to add entries using Ztransaction to store Amex card number, employee number and to build logic to pull proper Amex card number using first 6digits and last 4digits.

I will be off tomorrow and I would like to see every day progress till this development is completed

Dt: 11/11/16 by BMS Raghav

As per the requirement we have to create the new payment method AMEX for remittance purpose and the payments will happen through ACH and also we are going to set up new payment method for ACH payments

There are
1) Use one payment method of ACH and AMEX remittance custom program.
2) Create two payment methods, one for AMEX, and one for ACH. The AMEX payment method will generate the ACH file for only AMEX vendors in addition to AMEX remittance file – even this should have the custom program

I think the 1st option is better than creating 2 separate payment methods because for both we should create custom program, we cannot create the two DMEE files for one payment method.

For this we need clarification on below points
1) How many vendors are there for AMEX globally? If 1 or 2 we can hard code them in program.
2) Will all company codes pay to AMEX with ACH or have their own respective banks and payment method?

Please let me know your views for further proceedings.

Dt: 11/15/16 - As per the call with Babu, Jingsha and Baiwa, NEC have opted for solution option 1 to create only custom program and payments will made in regular ACH payments.



In Scope:

[List the activities to be included in scope]

Out of Scope:

[Out of scope activities]

Solution Summary

[Discuss this section with Requester and get approval prior to beginning work]

AMEX remittance file can be created with custom program with specific to payment run date, identification and amex vendors. This program can be run for any company code irrespective of payment method as Amex cards are used globally in NEW ERA and make payments with different payment methods.

1) Create custom table ZFI_AMEX_EMP with transaction ZAMEX with below fields
Company code
 EMP ID
 Vendor
 AMEX Card No








2) Create new Z program with below screen inputs
Run Date
 REGUP-LAUFD
Identification
 REGUP-LAUFI

Below are the steps to pull data from SAP to input in custom program

 Required fields for development
 Logic to get the info for fields
Step 1
 AMEX vendors
 Pick the AMEX vendors as per screen input and validate it with the custom table, if matched then pass in table REGUP-LIFNR and REGUP-LAUFD
Step 2
 AMEX vendors invoices
 Pass run date, vendors and company code from input screen to table REGUP to pick all the invoices/documents REGUP-BELNR and REGUP-GJAHR
Step 3
 AMEX credit cards
 To get the credit card numbers input the invoice in BSEG with company code and fiscal year to get the first 6 digits and last 4 digits from BSEG-SGTXT and match with the ZAMEX custom table
Step 4
 AMEX credit card amount
 Pick the amount from BSEG-WRBTR should match with posting key BSEG-BSCHL 31 and document no. BSEG-BELNR, if the credit card have multiple invoices sum up and show in as one record in remittance file
Logic to generate the file through custom program
Header
LOC
 Lenghth
 File Input
 SAP Fields
 Logic
1-2
 2
 00
 Hardcode
 Record Type
3-12
 10
 015147
 Hardcode with leading zeros
 Load Number (Assigned)
13-20
 8
 YYYYMMDD
 REGUP-LAUFD
 Creation Date (yyyymmdd)
21-26
 6
 160503
 Hardcode
 CID (Assigned)
27-30
 4
 6147
 Hardcode
 Book Number (Assigned)
31-32
 2
 00
 Hardcode
 Processing Indicator
33-34
 2
 TT
 Hardcode
 Test File Indicator
35-36
 2
 01
 Number varies for each run
 Input from TVRVC table to get numeric series starts with 01
E.G. first file will contain 01, second file 02, 99th file 99, 100th file rolls back to 01
37-220
 184
Hardcode
 Filler
Item
LOC
 BYTES
 File Input
 SAP Fields
 Logic
1-2
 2
 10
 Hardcode value 10
 Record Type
3
 1
 -
 Hardcode '-'
 Credit or Debit Amt Identifier
4-15
 12
 $$$$$$$$$$$$
 REGUP-WRBTR with leading zeros
 Proceed as Step 4
16-30
 15
 AMEXACCOUNT####
 Custom table ZAMEX
 Proceed as Step 3
31-99
 69
 Space
 Hardcode space ' '
100-101
 2
 01
 Message code hard code value 03
102-103
 2
Hard code space ' '
 Fill space for the length
104-119
 16
Hard code space ' '
 Fill space for the length
120-121
 2
Hard code space ' '
 Fill space for the length
122-137
 16
Hard code space ' '
 Fill space for the length
138-139
 2
Hard code space ' '
 Fill space for the length
140-155
 16
Hard code space ' '
 Fill space for the length
156-220
 65
 Spaces
 Hard code space ' '
 Fill space for the length
Total record
LOC
 BYTES
 File Input
 SAP Fields
 Logic
1-2
 2
 99
 Hardcode value 99
 Record Type
3
 1
 space
 Hard code space ' '
 Filler
4-15
 12
 TOTAL$$$$$$$
 Sum of all amount in item record with leading zeros
16-21
 6
 #RECRD
 Total count of item remittance amount with spaces at end
 Corporate Card Remittance Total Records
22
 1
 space
 Hardcode
 Filler
23-40
 18
 000000000000000000
 Hardcode
 Zero filled
41-220
 180
Hardcode
 Filler

Test Plan

[List test scenarios/cases to be executed here]

1) AMEX remittance file generation through custom program
Test ID
 Test Scenario
 Expected Results
FI-001
 AMEX remittance for company code 0010 with single invoice per file
 - File name should be in NEWERA.USREMI_RundateIDENT
- File should seperate the lines as per card mentioned in invoice and table ZAMEX_IBCP
- If card maintained in invoice not matched with ZAMEX_IBCP then invoice details should show in error log
- If card details are not mentioned in invoice then no information shown in log
FI-002
 AMEX remittance for multiple company codes 0010 and 0011 and with multiple invoices per vendor
 - File name should be in NEWERA.USREMI_RundateIDENT
- File should seperate the lines as per card mentioned in invoice and table ZAMEX_IBCP
- All invoice with same card and vendor should sumup and show as single in file
- If card maintained in invoice not matched with ZAMEX_IBCP then invoice details should show in error log
- If card details are not mentioned in invoice then no information shown in log
FI-003
 AMEX remittance for multiple company codes 0010 and 0011 and with multiple invoices per multiple vendors
 - File name should be in NEWERA.USREMI_RundateIDENT
- File should seperate the lines as per card mentioned in invoice and table ZAMEX_IBCP
- All invoice with same card and vendor should sumup and show as single in file
- If card maintained in invoice not matched with ZAMEX_IBCP then invoice details should show in error log
- If card details are not mentioned in invoice then no information shown in log

Solution Details

[Provide complete technical details for configuration or programming here]



Issues

[List Issues / Bugs identified in configuration or development]

1
 Create Custom table ZFI_AMEX_EMP















Company code
 EMP ID
 Vendor
 AMEX Card No







































2
 Create new Z program






Screen inputs







Run Date
 REGUP-LAUFD






Identification
 REGUP-LAUFI






Vendor
 REGUP-LIFNR
 Must be available in custom table













Required fields for development
 Logic to get the info for fields
Step 1
 AMEX vendors
 Pick the AMEX vendors as per hardcode/custom table and input in table REGUP with identificaton and run date
Step 2
 AMEX vendors invoices
 From table REGUP pick all the invoices/documents REGUP-BELNR, REGUP-BUKRS and REGUP-GJAHR
Step 3
 AMEX credit cards
 To get the credit card numbers input the invoice in BSEG to get the first 6 digits and last 4 digits from BSEG-SGTXT and match with the ZAMEX custom table
Step 4
 AMEX credit card amount
 Pick the amount from REGUP-WRBTR, if the credit card have multiple invoices sum up and show in as one record in remittance file









Header



LOC
 Lenghth
 File Input
 SAP Fields
 Logic



1-2
 2
 00
 Hardcode
 Record Type



3-12
 10
 015147
 Hardcode
 Load Number (Assigned)



13-20
 8
 YYYYMMDD
 REGUP-LAUFD
 Creation Date (yyyymmdd)



21-26
 6
 160503
 Hardcode
 CID (Assigned)



27-30
 4
 6147
 Hardcode
 Book Number (Assigned)



31-32
 2
 00
 Hardcode
 Processing Indicator



33-34
 2
 TT
 Hardcode
 Test File Indicator



35-36
 2
 01
 Number varies for each run
 File Sequence Number



37-220
 184
Hardcode
 Filler



Item







LOC
 BYTES
 File Input
 SAP Fields
 Logic



1-2
 2
 10
 Hardcode value 10
 Record Type



3
 1
 -
 Hardcode '-'
 Credit or Debit Amt Identifier



4-15
 12
 $$$$$$$$$$$$
 REGUP-WRBTR
 Proceed as Step 4



16-30
 15
 AMEXACCOUNT####
 Custom table ZAMEX
 Proceed as Step 3



31-99
 69
 Space
 Hardcode space ' '




100-101
 2
 01
 Message code hard code value 03




102-103
 2
Hard code space ' '
 Fill space for the length



104-119
 16
Hard code space ' '
 Fill space for the length



120-121
 2
Hard code space ' '
 Fill space for the length



122-137
 16
Hard code space ' '
 Fill space for the length



138-139
 2
Hard code space ' '
 Fill space for the length



140-155
 16
Hard code space ' '
 Fill space for the length



156-220
 65
 Spaces
 Hard code space ' '
 Fill space for the length



Total record







LOC
 BYTES
 File Input
 SAP Fields
 Logic



1-2
 2
 99
 Hardcode value 99
 Record Type



3
 1
 space
 Hard code space ' '
 Filler



4-15
 12
 TOTAL$$$$$$$
 Sum of all amount in item record




16-21
 6
 #RECRD
 Total count of item remittance amount
 Corporate Card Remittance Total Records



22
 1
 space
 Hardcode
 Filler



23-40
 18
 000000000000000000
 Hardcode
 Zero filled



41-220
 180
Hardcode
 Filler